#include <boost/lexical_cast.hpp>
#include "sql_Connector.h"

sql_Connector::sql_Connector()
{
	int ab;
	server=Server; 
	user = USR ; 
	password = PWD; 
	Initialize();
}

sql_Connector::~sql_Connector()
{
	if (con!=NULL)
		delete con;
	if (stmt!=NULL)
		delete stmt;
	if (res!=NULL)
		delete res;
}

void sql_Connector::Initialize()
{
	driver=NULL; con=NULL; stmt=NULL; res=NULL;
}

sql_Connector::sql_Connector(string server_in, string usr_in, string pwd_in)
{
	server=server_in;
	user=usr_in;
	password=pwd_in;
	Initialize();
}

/* 
	Connect to a database with server, username and password define in the constructor
*/
void sql_Connector::start_Connect()
{
	try {
		driver = get_driver_instance();
		con = driver->connect(server, user, password);
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what();
		system("PAUSE");
		exit(1);
	}
}

void sql_Connector::start_Connect(string DB_in)
{
	try {
		driver = get_driver_instance();
		con = driver->connect(server, user, password);
		if (con!=NULL) con->setSchema(DB_in);
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what();
		system("PAUSE");
		exit(1);
	}
}

/*
	choose database specified by DB_in
*/
void sql_Connector::chooseDB(string DB_in)
{
	if (con!=NULL)
		con->setSchema(DB_in);
}

/*
	create database specified by DB_in
*/
void sql_Connector::createDB(string DB_in)
{
	if (stmt!=NULL)
		delete stmt;
	if (con==NULL)
		con = driver->connect(server, user, password);
	try{
		stmt = con->createStatement();
		stmt->execute("create database IF NOT EXISTS " + DB_in);
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what();
		system("PAUSE");
		exit(1);
	}

}
/*
	execute a query from a file
*/
void sql_Connector::execute_from_FILE(string file_name)
{
	fstream inStream(file_name);

	if (inStream)
	{
		std::string contents;
		inStream.seekg(0, std::ios::end);
		contents.resize(inStream.tellg());
		inStream.seekg(0, std::ios::beg);
		inStream.read(&contents[0], contents.size());
		try{
			stmt = con->createStatement();
			stmt->execute(contents);
		}
		catch (sql::SQLException &e) {
			std::cout<<e.what();
			system("PAUSE");
			exit(1);
		}
		inStream.close();
	}
}

/*
	Populate the database from the file given
*/
void sql_Connector::insert_from_FILE(string file_name)
{
	
	std::string delimiter = ":";
	int row=0;
	int pos = 0;
	int prev_pos=0;
	int z=0;
	string line;
	string token;
	ifstream read;
	try{
		stmt=con->createStatement();
		string query="INSERT IGNORE INTO item(itemName, category, manufacturer, itemId, price, local_stock,Ostock, minimum_stock, bundle_unit) VALUES";
		read.open(file_name);
		if(read.is_open())
		{
			cout << "File ./" << file_name << " is open.\n";
			while (!read.eof())
			{
				getline(read,line);
				if (line.length()<=1)
					continue;
				z=0;
				prev_pos=0;
				if (row)
					query+=" , ";
				while ((pos = line.find(delimiter,prev_pos)) != std::string::npos) {
					string tmp;
					int siz;
					int k;
					token = line.substr(prev_pos, pos-prev_pos);
					prev_pos=pos+1;
					if (z==0)
						query+="(";
					if (z<3)
					{
						tmp="";
						siz=token.length();
						for (k=0;k<siz;k++)
							if (token[k]!='\'')
								tmp+=token[k];
							else
								tmp+="\'\'";
						query+= "\'"+ tmp + "\'"+" , ";
					}
					else
						query+=token+" , ";
					if (z==5)
						query+=token+" , ";
					z++;
				}
				token=line.substr(prev_pos,line.length()-prev_pos);
				query+=token+" ) ";
				row++;
				// Prevent overload the string
			}
			if (row)
				stmt->execute(query);
		}
		else
			cout << "Error opening " << file_name << ".\n";

		delete stmt;
		stmt=NULL;
	}
	catch (sql::SQLException &e) {
			std::cout<<e.what()<<endl;
			system("PAUSE");
			exit(1);
	}

}

void sql_Connector::insert_Transaction_from_FILE(string file_name)
{
	populate_Cashier_from_FILE(file_name,1234);
	std::string delimiter = ":";
	int pos = 0;
	int z=0;
	int row=0;
	string line;
	string token;
	ifstream read;
	try{
		stmt=con->createStatement();
		string query="INSERT IGNORE INTO local_transaction(tId, cId, itemId, Unit_Sold,tDate) VALUES ";
		read.open(file_name);

		if(read.is_open())
		{
			cout << "File ./" << file_name << " is open.\n";
			while (!read.eof())
			{
				getline(read,line);
				if (line.length()<=1)
					continue;
				z=0;
				if (row)
					query+=" , ";
				while ((pos = line.find(delimiter)) != std::string::npos) {
					z++;
					if (z==1)
						query+="(";
					token = line.substr(0, pos);
					line.erase(0, pos + delimiter.length());
					if (z==3)
						continue;
					query+=token + " , ";				
				}
				token=toDate(line);
				query+="\'"+token +"\'"+" ) ";
				row++;
			}
			stmt->execute(query);
			
		}
		else
			cout << "Error opening " << file_name << ".\n";

		delete stmt;
		stmt=NULL;
	}
	catch (sql::SQLException &e) {
			std::cout<<e.what()<<endl;
			system("PAUSE");
			exit(1);
	}
}

void sql_Connector::insert_Transaction_toHQ_from_FILE(string file_name)
{
	std::string delimiter = ":";
	int pos = 0;
	int z=0;
	string line;
	string token;
	ifstream read;
	try{
		sql::PreparedStatement  *prep_stmt;
		prep_stmt = con->prepareStatement("INSERT IGNORE INTO local_transaction(itemId, Unit_Sold,tDate,shopID) VALUES (?, ?, ?, ?)");
		read.open(file_name);
		if(read.is_open())
		{
			cout << "File ./" << file_name << " is open.\n";
			while (!read.eof())
			{
				getline(read,line);
				z=0;
				while ((pos = line.find(delimiter)) != std::string::npos) {
					token = line.substr(0, pos);
					line.erase(0, pos + delimiter.length());
					switch(z)
					{
					case itemID: case unit_sold: 
						prep_stmt->setInt(z+1, atoi(token.c_str()));
						break;
					case tDate:
						prep_stmt->setDateTime(z+1, token.c_str());
						break;
					}
					z++;
				}
				prep_stmt->setDateTime(3, line.c_str());
				prep_stmt->setInt(4,SHOP_ID);
				prep_stmt->executeUpdate();
			}
		}
		else
			cout << "Error opening " << file_name << ".\n";
		
		delete prep_stmt;
	}
	catch (sql::SQLException &e) {
			std::cout<<e.what()<<endl;
			system("PAUSE");
			exit(1);
	}
}


void sql_Connector::insert_Transaction_toHQ_from_FILE_v2(string file_name,string shopID)
{
	sql::Statement *stmt2;
	
	std::string delimiter = ":";
	int row=0;
	int pos = 0;
	int prev_pos=0;
	int z=0;
	string line;
	string token;
	ifstream read;

	try{
		stmt2=con->createStatement();
		string query="INSERT INTO local_transaction(itemId, Unit_Sold,tDate,shopID) VALUES ";
		read.open(file_name);
		if(read.is_open())
		{
			cout << "File ./" << file_name << " is open.\n";
			while (!read.eof())
			{
				getline(read,line);
				if (line.length()<=1)
					continue;
				z=0;
				prev_pos=0;
				if (row)
					query+=" , ";
				while ((pos = line.find(delimiter,prev_pos)) != std::string::npos) {
					string tmp;
					int siz;
					int k;
					token = line.substr(prev_pos, pos-prev_pos);
					prev_pos=pos+1;
					if (z==0)
						query+="(";
					if (z<3)
					{
						z++;
						continue;
					}
					else
					{
						query+=token+" , ";
					}
					z++;
				}
				token=line.substr(prev_pos,line.length()-prev_pos);
				token=toDate(token);
				query+="\'"+token +"\'";

				query+= " , " + shopID + " ) ";
				row++;
				// Prevent overload the string
			}

			query+= " ON DUPLICATE KEY UPDATE unit_sold=unit_sold + VALUES(Unit_Sold)";
			if (row)
				stmt2->execute(query);
		}
		else
			cout << "Error opening " << file_name << ".\n";

		delete stmt2;
	}
	catch (sql::SQLException &e) {
			std::cout<<e.what()<<endl;
			system("PAUSE");
			exit(1);
	}

}
void sql_Connector::populate_cashier_test()
{
	std::string delimiter = ":";
	int pos = 0;
	int z=0;
	string line;
	string token;
	ifstream read;
	try{
		sql::PreparedStatement  *prep_stmt;
		prep_stmt = con->prepareStatement("INSERT INTO cashier(cashierID, shopID ) VALUES (?,?)");
		prep_stmt->setInt(1,1693);
		prep_stmt->setInt(2,1693);
		prep_stmt->executeUpdate();
		delete prep_stmt;
	}
	catch (sql::SQLException &e) {
			std::cout<<e.what()<<endl;
			system("PAUSE");
			exit(1);
	}
}

void sql_Connector::populate_Cashier_from_FILE(string file_name,int SID)
{
	std::string delimiter = ":";
	int pos = 0;
	int z=0;
	int row=0;
	string line;
	string token;
	ifstream read;
	map<string,string> Icash;
	try{
		Icash.clear();
		stmt=con->createStatement();
		string query="INSERT IGNORE INTO cashier(cashierID, cashier_name ) VALUES ";
		read.open(file_name);
		if(read.is_open())
		{
			cout << "File ./" << file_name << " is open.\n";
			while (!read.eof())
			{
				getline(read,line);
				if (line.length()<=1)
					continue;
				z=0;
				while ((pos = line.find(delimiter)) != std::string::npos) {
					token = line.substr(0, pos);
					line.erase(0, pos + delimiter.length());
					if (z==1)
					{
						// Exist cashier then break,else insert cashier
						if (Icash.find(token)==Icash.end())
						{
							Icash.insert(std::pair<string,string>(token,"1234"));
							if (row)
								query+=" , ";
							query+="("+token+ " , "+"'1234')";
							row++;
							break;
						}
						else
							break;
					}			
					z++;
				}
			}
			stmt->execute(query);
		}
		else
			cout << "Error opening " << file_name << ".\n";
		
		delete stmt;
		stmt=NULL;
	}
	catch (sql::SQLException &e) {
			std::cout<<e.what()<<endl;
			system("PAUSE");
			exit(1);
	}
}

string sql_Connector::toDate(string toCut)
{
	std::string delimiter = "/";
	string result="";
	string arr[3];
	string token;
	int pos = 0;
	int z=0;
	while ((pos = toCut.find(delimiter)) != std::string::npos) {
		token = toCut.substr(0, pos);
		toCut.erase(0, pos + delimiter.length());
		arr[z]=token;
		z=z+1;
	}
	arr[z]=toCut;
	result=arr[2]+'-'+arr[1]+'-'+arr[0];
	return result;
}

/*
	This function take the barcode and search the item based on it
	success: return 1;
	no item: return 0;
*/
int sql_Connector::search_from_barcode(int barcode,Item *Ifind)
{
	try
	{
		int count=0;
		ostringstream convert;   // stream used for the conversion
		convert << barcode;      // insert the textual representation of 'Number' in the characters in the stream
		string query="SELECT * FROM item i WHERE i.itemId=" + convert.str()  ; 
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		double price;
		while (res->next())
		{
			count++;
			Ifind->set_ItemID(res->getInt("itemId"));
			Ifind->set_ItemName(res->getString("itemName"));
			Ifind->set_ItemCategory(res->getString("category"));
			Ifind->set_ItemManufacturer(res->getString("manufacturer"));
			// dynamic pricing
			price=calculate_Price(res->getInt("local_stock"),res->getInt("Ostock"),res->getDouble("price"));
			Ifind->set_ItemPrice(price);
			Ifind->set_ItemLocalStock(res->getInt("local_stock"));
			Ifind->setItemMinStock(res->getInt("minimum_stock"));
			Ifind->setItemBundleUnit(res->getInt("bundle_unit"));
		}
		if (count!=1)
			return 0;
		else
			return 1;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}

}

/*
	This function takes in the string barcode, name, category and manufacture( allow empty string)
	Compute the search base on the info given
	Return 0 if find at
*/
int sql_Connector::search_general(string barcode,string iName,string iCate, string iManu,vector<Item*> &iList)
{
	if (!(barcode.length() || iName.length() || iCate.length() || iManu.length()))
		return 0;

	string query=makeSearchQuery(barcode,iName,iCate,iManu);
	Item *Ifind;
	double price;
	try
	{
		int count=0;
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		while (res->next())
		{
			count++;
			Ifind=new Item();
			Ifind->set_ItemID(res->getInt("itemId"));
			Ifind->set_ItemName(res->getString("itemName"));
			Ifind->set_ItemCategory(res->getString("category"));
			Ifind->set_ItemManufacturer(res->getString("manufacturer"));
			// dynamic pricing
			price=calculate_Price(res->getInt("local_stock"),res->getInt("Ostock"),res->getDouble("price"));
			Ifind->set_ItemPrice(price);

			Ifind->set_ItemLocalStock(res->getInt("local_stock"));
			Ifind->setItemMinStock(res->getInt("minimum_stock"));
			Ifind->setItemBundleUnit(res->getInt("bundle_unit"));
			iList.push_back(Ifind);
		}
		if (count<1)
			return 0;
		else
			return 1;
		delete stmt;
		stmt=NULL;
		delete res;
		res=NULL;
	}
	catch (sql::SQLException &e) {
		cout<< e.what() << endl;
		return 0;
	}
}

int sql_Connector::searchGeneralStockExceedsMinStock(string barcode, string iName, string iCate, string iManu, vector<Item*> &iList) {
	string query=makeSearchQuery(barcode,iName,iCate,iManu);
	query += " and i.local_stock <= i.minimum_stock";
	//query += boost::lexical_cast<string>(minStock);

	//query += " LIMIT 0 , 25";
	Item *Ifind;
	double price;
	try
	{
		int count=0;
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		while (res->next())
		{
			count++;
			Ifind=new Item();
			Ifind->set_ItemID(res->getInt("itemId"));
			Ifind->set_ItemName(res->getString("itemName"));
			Ifind->set_ItemCategory(res->getString("category"));
			Ifind->set_ItemManufacturer(res->getString("manufacturer"));
			// dynamic pricing
			price=calculate_Price(res->getInt("local_stock"),res->getInt("Ostock"),res->getDouble("price"));
			Ifind->set_ItemPrice(price);
			Ifind->set_ItemPrice(res->getDouble("price"));
			Ifind->set_ItemLocalStock(res->getInt("local_stock"));
			Ifind->setItemMinStock(res->getInt("minimum_stock"));
			Ifind->setItemBundleUnit(res->getInt("bundle_unit"));
			iList.push_back(Ifind);
		}
		if (count<1)
			return 0;
		else
			return 1;
		delete stmt;
		stmt=NULL;
		delete res;
		res=NULL;
	}
	catch (sql::SQLException &e) {
		cout<< e.what() << endl;
		return 0;
	}
}

string sql_Connector::makeSearchQuery(string barcode,string iName,string iCate, string iManu)
{
	string result="SELECT * FROM item i WHERE ";
	string bar,Name,Cate,Manu;

	if (barcode.length()==0)
		result+="i.itemId=i.itemId and ";
	else
		result+="i.itemId= "+barcode+ " and ";

	if (iName.length()==0)
		result+="i.itemName=i.itemName and ";
	else
		result+="i.itemName LIKE \'%"+iName+ "%\' and ";

	if (iCate.length()==0)
		result+="i.category=i.category and ";
	else
		result+="i.category LIKE \'%"+iCate+ "%\' and ";

	if (iManu.length()==0)
		result+="i.manufacturer=i.manufacturer ";
	else
		result+="i.manufacturer LIKE \'%"+iManu+"%\'";

	return result;
}

/*
	Insert a new transaction, date is current date
*/
int sql_Connector::insert_new_Transaction(string barcode, string iQuantity,string cashierID)
{
	try
	{
		//Update the Stock first
		update_stock(barcode,iQuantity,REDUCE);

		string query="SELECT MAX(l.tId) AS highest_tID , l.tDate FROM local_transaction l WHERE l.tDate=CURDATE()" ; 
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		res->first();
		int newtID=res->getInt("highest_tID")+1;
		string curDate=res->getString("tDate");
		// If this is the first transaction of a new day, we get the date of that day
		if (newtID==1)
		{
			query= "SELECT CURDATE( ) AS DATE";
			stmt=con->createStatement();
			res=stmt->executeQuery(query);
			res->first();
			curDate=res->getString("DATE");
		}
		delete stmt;
		stmt=NULL;
		delete res;
		res=NULL;

		int cId=atoi(cashierID.c_str());
		int itemId=atoi(barcode.c_str());
		int Unit_Sold=atoi(iQuantity.c_str());

		sql::PreparedStatement  *prep_stmt;
		prep_stmt = con->prepareStatement("INSERT INTO local_transaction(tId, cId, itemId, Unit_Sold,tDate) VALUES (?, ?, ?, ?, ?)");
		prep_stmt->setInt(1,newtID);
		prep_stmt->setInt(2,cId);
		prep_stmt->setInt(3,itemId);
		prep_stmt->setInt(4,Unit_Sold);
		prep_stmt->setString(5,curDate);

		prep_stmt->executeUpdate();
		delete prep_stmt;
		prep_stmt=NULL;
		return 1;
	}
	catch (sql::SQLException &e) {
		return 0;
	}
}
int sql_Connector::insert_new_Transaction_list(vector<vector<string>> iTrans)
{
	string curDate;
	string query="SELECT MAX(l.tId) AS highest_tID , l.tDate FROM local_transaction l WHERE l.tDate=CURDATE()" ; 
	stmt=con->createStatement();
	res=stmt->executeQuery(query);
	res->first();
	int newtID=res->getInt("highest_tID")+1;
	// If this is the first transaction of a new day, we get the date of that day
	if (newtID==1)
	{
		sql::Statement *stmt2;
		sql::ResultSet *res2;
		string query2= "SELECT CURDATE( ) AS DATE ";
		stmt2=con->createStatement();
		res2=stmt2->executeQuery(query2);
		res2->first();
		curDate=res2->getString("DATE");
		delete stmt2;
		delete res2;
	}
	else
		curDate=res->getString("tDate");

	query="INSERT IGNORE INTO local_transaction(tId, cId, itemId, Unit_Sold,tDate) VALUES";
	string newTiD_str=boost::lexical_cast<string>(newtID);
	try
	{
		int size=iTrans.size();
		for (int j=0;j<size;j++)
		{

			string barcode=iTrans[j][barcod];
			string iQuantity=iTrans[j][quantit];
			string cashierID=iTrans[j][cashi];
			//Update the Stock first
			update_stock(barcode,iQuantity,REDUCE);


			query+="("+newTiD_str+", "+ cashierID+", "+barcode+", "+iQuantity+", " + "\'" + curDate+"\'"+")";
			if (j<size-1)
				query+=" , ";

		}
		stmt->execute(query);
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what();
		system("PAUSE");
		exit(1);
	}

	delete stmt;
	stmt=NULL;
	delete res;
	res=NULL;
	return 1;
}

/*
	This function take the cId and search the cashier based on it
	success: return 1;
	no item: return 0;
*/
int sql_Connector::search_cashier_from_cId(int cId)
{
	try
	{
		int count=0;
		ostringstream convert;   // stream used for the conversion
		convert << cId;      // insert the textual representation of 'Number' in the characters in the stream
		string query="SELECT * FROM cashier c WHERE c.cashierId=" + convert.str(); 
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		while (res->next())
		{
			count++;
		}
		
		if (!count)
			return 0;
		else
			return 1;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

/*
	Update an item stock based on bardcode and quantity reduce
	1: success
	0: unsuccesful
*/
int sql_Connector::update_stock(string barcode, string iQuantity, UpStock type)
{
	try
	{
		string sign;
		if (type== INCREASE)
			sign = "+";
		else
			sign = "-";
		sql::Statement *stmt2=con->createStatement();
		string query="UPDATE item i SET i.local_stock=i.local_stock " + sign + " " + iQuantity + " WHERE i.itemId=" + barcode ; 
		stmt2=con->createStatement();
		
		if (stmt2->executeUpdate(query))
		{
			delete stmt2;
			return 1;
		}
		else
		{
			delete stmt2;
			return 0;
		}
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

double sql_Connector::calculate_Price(int curStock, int Ostock, double Oprice)
{

	double result=Oprice*100;
	int tmp=floor(result+0.5);
	if (tmp>=10000)
		tmp=9999;
	result=tmp/100.0;
	return result;
}


int sql_Connector::get_Curdate_Transaction(vector<Transaction*> &iList)
{
	try
	{
		Transaction *Itrans;
		int count=0;
		string query="SELECT * FROM  local_transaction l, item i WHERE l.tDate=CURDATE() and i.itemId=l.itemId ORDER BY l.tId ASC" ; 
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		while (res->next())
		{
			count++;
			Itrans=new Transaction();
			Itrans->set_cId(res->getInt("cId"));
			Itrans->set_tId(res->getInt("tId"));
			Itrans->set_iId(res->getInt("itemId"));
			Itrans->set_Date(res->getString("tDate"));
			Itrans->set_Quantity(res->getInt("Unit_Sold"));
			Itrans->set_Price(res->getDouble("price"));
			iList.push_back(Itrans);
		}
		if (count!=1)
			return 0;
		else
			return 1;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

int sql_Connector::get_Curdate_Transaction_Consolidate(vector<Transaction*> &iList)
{
	try
	{
		Transaction *Itrans;
		int count=0;
		string query="SELECT l.itemId,SUM(l.Unit_Sold) as total_stock,l.tDate FROM local_transaction l WHERE l.tDate=curDate()  GROUP BY l.itemId" ; 
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		while (res->next())
		{
			count++;
			Itrans=new Transaction();
			Itrans->set_iId(res->getInt("itemId"));
			Itrans->set_Date(res->getString("tDate"));
			Itrans->set_Quantity(res->getInt("total_stock"));
			iList.push_back(Itrans);
		}
		delete stmt;
		stmt=NULL;
		delete res;
		res=NULL;
		if (count!=1)
			return 0;
		else
			return 1;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

int sql_Connector::get_CurMonth_Transaction_Consolidate(vector<Transaction*> &iList)
{
	try
	{
		Transaction *Itrans;
		int count=0;
		string query="SELECT l.itemId,SUM(l.Unit_Sold) as total_stock,l.tDate FROM local_transaction l WHERE l.tDate<curDate()  GROUP BY l.itemId,l.tDate" ; 
		stmt=con->createStatement();
		res=stmt->executeQuery(query);
		while (res->next())
		{
			count++;
			Itrans=new Transaction();
			Itrans->set_iId(res->getInt("itemId"));
			Itrans->set_Date(res->getString("tDate"));
			Itrans->set_Quantity(res->getInt("total_stock"));
			iList.push_back(Itrans);
		}

		delete stmt;
		stmt=NULL;
		delete res;
		res=NULL;
		if (count!=1)
			return 0;
		else
			return 1;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

string sql_Connector::getCurdate()
{
	try
	{
		sql::Statement *stmt2;
		sql::ResultSet *res2;
		string query="SELECT CURDATE() AS DATE";
		string result;
		stmt2=con->createStatement();
		res2=stmt2->executeQuery(query);
		while (res2->next())
		{
			result=res2->getString("DATE");
		}
		delete stmt2;
		delete res2;
		return result;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

void sql_Connector::clearItem()
{
	try
	{
		string query="DELETE FROM item WHERE 1";
		string result;
		stmt=con->createStatement();
		stmt->execute(query);
		delete stmt;
		stmt=NULL;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

void sql_Connector::clearPriceDisplay()
{
	try
	{
		string query="DELETE FROM pricedisplay WHERE 1";
		string result;
		stmt=con->createStatement();
		stmt->execute(query);
		delete stmt;
		stmt=NULL;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

void sql_Connector::map_PriceDisplay()
{
	try
	{
		sql::Statement *stmt2=con->createStatement();
		sql::ResultSet *res2; 
		string query="SELECT i.itemId FROM item i";
		res2=stmt2->executeQuery(query);
		int pId=0;
		string pId_s;
		string barcode;
		query="INSERT INTO pricedisplay(pId,itemId) VALUES";
		while (res2->next() && pId<MAX_PID)
		{
			pId++;
			pId_s=boost::lexical_cast<string>(pId);
			barcode=boost::lexical_cast<string>(res2->getInt("itemId"));
			if (pId>1)
				query+=" ,";
			query+= " ( " + pId_s + ',' + barcode + " )";
		}
		stmt2->execute(query);
		delete stmt2;
		delete res2;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

string sql_Connector::search_from_pId(string pId)
{
	try
	{
		string result="";
		sql::Statement *stmt2=con->createStatement();
		sql::ResultSet *res2; 
		string query="SELECT p.itemId FROM pricedisplay p WhERE p.pId="+pId;
		res2=stmt2->executeQuery(query);
		while (res2->next())
		{
			result=boost::lexical_cast<string>(res2->getInt("itemId"));
		}
		
		delete stmt2;
		delete res2;
		return result;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

int sql_Connector::clear_PID(string pId)
{
	try
	{
		string result="";
		sql::Statement *stmt2=con->createStatement();
		string query="DELETE FROM pricedisplay WhERE pId ="+pId;
		stmt2->execute(query);
		delete stmt2;

		return 1;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

int sql_Connector::add_PID(string pId,int new_Barcode)
{
	try
	{
		string result="";
		sql::Statement *stmt2=con->createStatement();
		string query="Insert into pricedisplay VALUES(" + pId + " , " + boost::lexical_cast<string>(new_Barcode) + " ) ON DUPLICATE KEY UPDATE itemId =" + boost::lexical_cast<string>(new_Barcode);
		stmt2->execute(query);
		delete stmt2;
		return 1;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

int sql_Connector::getTotal_PriceDisplay()
{
	try
	{
		int result;
		sql::ResultSet *res2;
		sql::Statement *stmt2=con->createStatement();
		string query="SELECT COUNT(*) as count FROM pricedisplay";
		res2=stmt2->executeQuery(query);
		res2->first();
		result=res2->getInt("count");
		delete stmt2;
		delete res2;
		return result;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

void sql_Connector::delete_cashiers()
{
	try
	{
		int result;
		sql::ResultSet *res2;
		sql::Statement *stmt2=con->createStatement();
		string query="Delete from cashier where 1";
		stmt2->execute(query);
		delete stmt2;

	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}

int sql_Connector::dynamic_Pricing()
{
	try
	{
		int result=1;
		sql::Statement *stmt2=con->createStatement();
		//int mul=(Ostock-curStock)*5/Ostock;
		//double result=Oprice+Oprice*mul/10.0;
		string query="Update item i Set i.price=i.price + i.price*(i.Ostock - i.local_stock)*5/(i.Ostock*10), i.Ostock=i.local_stock Where i.Ostock >0";
		stmt2->execute(query);
		query="Update item i Set i.price=99.99 WHere i.price>99.99";
		stmt2->execute(query);
		delete stmt2;
		return result;
	}
	catch (sql::SQLException &e) {
		std::cout<<e.what()<<endl;
		system("PAUSE");
		exit(1);
	}
}